﻿/******************************************************    
文件名:operate.cs
版权  (c)  2010-11-23 淘渺圈圈工作室
作者:黄媛媛
创建日期:2010-12-03
维护人员:黄媛媛
维护日期:2010-12-03
文件功能描述:金属材料热处理网络学习平台_访问movie数据表操作
******************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Data;

namespace Mmht.DB
{
    public class TableMovieDB
    {
        /// </summary>
        /// Movie查询
        /// 
        /// <param name="i">查询类型</param>
        /// 1查询全部的Movie
        /// 2查询推荐Movie
        /// 3查询点击率最高的100部Movie
        /// <returns>返回datatable</returns>
        /// Writer：黄媛媛
        /// Create Date：2010-12-02
        public DataTable Get_CTRtop_Movie(int number)
        {
            DataTable dt = new DataTable();
            string sql = string.Empty;
            sql = "select top "+number.ToString()+" * from movie order by viewnum desc";
            operate op = new operate();
            dt = op.Query(sql);
            return dt;
        }
        public DataRow GetmovieById(int id)
        {
            string sql = "select * from movie where id = " + id.ToString();
            DataTable dt = new DataTable();
            operate op = new operate();
            dt = op.Query(sql);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0];
            }
            return null;
        }

        public void addMovieViewNum(int id)
        {
            string sql = @"update movie set viewnum = viewnum + 1 where id = " + id.ToString() + " ";
            operate op = new operate();
            bool s = op.Execute(sql);
        }
        public bool DelMovie(int id) 
        {
            string sql = string.Empty;
            sql = "delete from movie where id="+id.ToString ()+"";
            operate op = new operate();
            bool ret = op.Execute(sql);
            return ret;
        }
        public bool UpdateMovie(string name,string author,string remark,int id) 
        {
            string sql = string.Empty;
            sql = "update movie set name_cn='"+name.ToString ()+"',author='"+author .ToString ()+"',remark='"+remark .ToString ()+"' where id="+id.ToString ()+"";
            operate op = new operate();
            bool rst = op.Execute(sql );
            return rst;
        }
        public DataTable GetMoviebyname(string name) 
        {
            string sql = string.Empty;
            sql = "select * from movie where name_cn ='"+name .ToString ()+"'";
            operate op = new operate();
            DataTable dt = new DataTable();
            dt = op.Query(sql);
            return dt;
        }
        public bool Recommend(int id) 
        {
            string sql = string.Empty;
            sql = "update movie set iscommend = 'true' where id="+id.ToString ()+"";
            operate op = new operate();
            bool rst = op.Execute(sql);
            return rst;
        }
        public bool Unrecommend(int id)
        {
            string sql = string.Empty;
            sql = "update movie set iscommend = 'false' where id ="+id.ToString ()+"";
            operate op = new operate();
            bool rst = op.Execute(sql);
            return rst;
        }
        public bool ClearViewnum(int id) 
        {
            string sql = string.Empty;
            sql = "update movie set viewnum = 0 where id = "+id.ToString ()+"";
            operate op = new operate();
            bool rst = op.Execute(sql);
            return rst;
        }
        public bool ClearAllViewnum() 
        {
            string sql = string.Empty;
            sql = "update movie set viewnum = 0";
            operate op = new operate();
            bool rst = op.Execute(sql);
            return rst;
        }

        public void UploadMovie(DataTable dt, int create_by, DateTime create_at)
        {
            string name_cn = string.Empty;
            string url = string.Empty;
            string sql = string.Empty;
            operate op = new operate();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                name_cn = dt.Rows[i][0].ToString();
                url = dt.Rows[i][1].ToString();
                DelMovie(name_cn);
                sql = @"INSERT INTO movie
                    (name_cn,created_at,created_by,movie_url,viewnum,iscommend,image_url)
                     VALUES
                    ('" + name_cn + "','" + create_at + "','" + create_by.ToString() + "','" + url + "',0,'false','images_cn/novideo.png')";
                op.Execute(sql);
            }
        }

        public bool DelMovie(string name_cn)
        {
            operate op = new operate();
            string sql = string.Empty;
            sql = "delete from movie where name_cn='" + name_cn + "'";
            bool bl = op.Execute(sql);
            return bl;
        }

        public DataTable UploadMovieInfo(DateTime create_at, int create_by)
        {
            operate op = new operate();
            string sql = string.Empty;
            sql = @"SELECT name_cn as 文件名,a.created_at as 上传时间,name as 用户名,movie_url as 文件路径,viewnum as 阅览次数
                    FROM movie a,admin b
                    WHERE a.created_by=b.id and a.created_at = '" + create_at.ToString("yyyy-MM-dd HH:mm:ss") + "' ";
            return op.Query(sql);
        }

        public DataTable SearchMovie(string key)
        {
            operate op = new operate();
            DataTable dt = new DataTable();
            string sql = string.Empty;
            sql = "select * from movie where name_cn like '%" + key + "%' order by created_at desc";
            dt = op.Query(sql);
            return dt;
        }

        public bool AddImage(int id, string imageUrl)
        {
            operate op = new operate();
            const string sqlconst = "update movie set image_url = '{0}' where id = '{1}'";
            string sql = string.Format(sqlconst, imageUrl, id.ToString());
            return op.Execute(sql);
        }
    }
}
